جستجو با تابع VLOOKUP در اکسل 💥تمام نکات کاربردی
سلام . به یکی دیگر از آموزش های سافت پلاس خوش آمدید. موضوعی که امروز می خواهیم راجع به آن با هم صحبت کنیم جستجو با تابع VLOOKUP در اکسل است.
تابع ویلوکاپ یکی از توابع کاربردی در نرم افزار اکسل است که از ان می شود در جستجو کردن و یافتن اطلاعات مورد نظر استفاده کرد .
این تابع در اکسل جزو توابع دسته refrence & lookup دسته بندی می شود .
قبلا در همین سایت در مورد تابعی به اسم لوکاپ که هم خانواده تابع ویلوکاپ در اکسل است در مطلب تابع Lookup در اکسلنکات کاربردی در مورد آن مفصل صحبت کردم .
اما امروز می خواهیم راجع به یکی از همین توابع کاربردی با هم صحبت کنیم .
تابعی به اسم Vlookup در اکسل .
اینکه این تابع چی هست ؟
از چه قسمتهایی تشکیل شده؟
و چه جوری می شود از آن در کار جستجوی داده ها استفاده کرد .و البته کلی نکته و ترفند کاربردی در مورد تابع ویلوکاپ که راجع به انها صحبت می کنیم .
پس از شما دعوت می کنم تا انتهای این آموزش همراه من باشید تا این تابع را به تمام معنا تبدیل به یک غول جستجو در اکسل کنیم .
فهرست محتوا
تابع Vlookup در اکسل قرار است چه کاری انجام دهد ؟
اگر بخواهیم عملکرد تابع وی لوکاپ را در یک جمله بیان کنیم باید گفت که :
” این تابع یک مقدار را از شما دریافت می کند و مقدار متناظر با آن را دقیقا از همان سطر اما در ستون دیگری که شما برایش مشخص می کنید برایتان بر می گرداند”
مطمئنا این تعریف برای شما تا حدودی گنگ و نامفهوم است .
اما برای اینکه بتوانید درک خوبی از این تعریف و نهایتا از عملکرد این تابع داشته باشید بهتر است تا انتهای این مطلب با ما همراه باشید .
اول بیایید نگاهی به خود اسم این تابع یعنی تابع ویلوکاپ بیاندازیم.
تابع vlookup در اکسل از دو جزء تشکیل شده است :
- حرف V در ابتدای این کلمه اشاره به کلمه Vertical دارد که به معنای عمودی می باشد.
- کلمه LOOKUP نیز به معنای جستجو می باشد .
اما این دو بخش که با هم نام یک تابع را تشکیل می دهند قرار است چه کاری را برای من انجام دهد ؟
در واقع با استفاده از تابع ویلوکاپ شما می توانید در ستون های یک جدول جستجو کنید و مقدار مورد نظر خودتان را با توجه به پارامترهایی که برای این تابع تعریف می کنید استخراج کنید .
بنابراین این تابع داده های موجود در یک محدوده را بصورت عمودی مورد جستجو قرار می دهد .
آشنایی با پارامترهای تابع VLOOKUP
بیایید با هم نگاهی به آرگومانهای مختلف این تابع بیاندازیم :
همانطور که مشاهده می کنید این تابع می تواند ۴ ورودی را بپذیرد حال ببینیم که این ورودی ها چه هستند :
lookup_value
با استفاده از این آرگومان به تابع می گویید که به دنبال چه مقداری می گردید.
این مقدار می تواند یک عدد یا متن یا تاریخ و یا آدرس یک سلول باشد .
table_array
در این آرگومان محدوده ای که باید مورد جستجو قرار گیرد را به تابع معرفی می نمایید.
این محدوده از اطلاعات می تواند دارای هرنوع داده ای اعم از متن ، تاریخ ، عدد و …. باشد .
col_index_num
این آرگومان شماره ستونی را که اطلاعات مورد نظر شما در آن قرار دارد را به تابع معرفی می کند
در ستون مذکور داده ای که در سلول همردیف با مورد جستجوی شماست بعنوان خروجی تابع خواهد بود .
range_lookup
توسط این آرگومان سطح دقت و حساسیت تابع را تعیین می کنید
بدین صورت که با دادن مقدار TRUE از حساسیت تابع کاسته و با دادن مقدار FALSE حساسیت تابع را بالا می برید استفاده از این پارامتر حالت اختیاری دارد .
بسیار خوب بیایید تمام این مفاهیم را بصورت عملی با چند مثال عینی دنبال کنیم.
مثال هایی کاربردی از تابع VLOOKUP در اکسل
برای شروع کار جدول اطلاعات زیر را در نظر بگیرید .
۱٫ می خواهیم سن فردی با کد پرسنلی ” ۰۵۵۶۹” را پیدا کنیم:
برای این منظور مراحل زیر را طی می کنیم .
=VLOOKUP("05569";A2:D10;4)
همانطور که گفتیم مورد جستجوی ما می تواند یک عدد باشد .
این مثال دقیقا همین مطلب را منعکس می کند .
در زبان محاوره این تابع را می توان اینگونه بیان نمود که :
1- ” عدد ۰۵۵۶۹ را در محدوده A2:D10 جستجو کن.
2- وقتی که این عدد را در این محدوده پیدا کردی به اندازه 4 ستون به جلو حرکت کن .
3- مقدار مربوط به سلولی که در همان ردیف از ستون چهارم قرار دارد را به من نمایش بده .
بیشتر بخوانید : ساخت فرم Splash Screen برای اکسل
اگر به جدول بالا دقت کنید متوجه خواهید شد که خروجی این تابع برای ما عدد ۳۷ خواهد بود .
عین این فرمول را می توان برای مقادیر متنی و یا تاریخ نیز به کار برد .
یعنی شما به تابع یک متن بدهید بعنوان مثال نام “سجاد ” را به تابع معرفی نموده و سن فرد را بعنوان خروجی دریافت کنید .
این کلیت کار با تابع وی لوکاپ در اکسل بود .
اما در ادامه با هم در قالب سوالاتی که مطرح می کنیم ، نکات بیشتری از این تابع را بررسی می کنیم .
ورودی غیر مستقیم تابع ویلوکاپ
در مثال های قبلی ما خود عبارتی را که می خواستیم پیدا کنیم را مستقیم در اختیار تابع قرار می دادیم .
اما این کار یک ایراد بزرگ دارد .
آن هم این است که هر بار که می خواهیم به دنبال یم مقدار جدید بگردیم باید به سراغ تابع رفته و مقدار جدید را جایگزین مقدار قبلی کنیم .
این کار می تواند برای افراد عادی که اطلاعی از عملکرد تابع ندارند مشکل باشد .
برای حل این مشکل از روش زیر استفاده می کنیم .
در این روش ما فقط آدرس یک سلول را به تابع معرفی می کنیم و از تابع می خواهیم هر مقداری را که در این سلول وارد شد در محدوده مورد نظر ما جستجو نماید .
با انجام این کار انعطاف بیشتری در عملکرد تابع بوجود می آید .
این مطلب را در ویدیو آموزشی که در ادامه همین مطلب درج خواهد شد می توانید مشاهده نمایید .
استفاده از ویلوکاپ در شیت های دیگر
مثال هایی که تا به اینجا بررسی نمودیم به گونه ای بود که هم تابع ما و هم جدول اطلاعاتی که در آن جستجو می کردیم همه در یک شیت قرار داشتند .
اما این تابع این قابلیت را دارد که با اطلاعات موجود بغیر از شیت جاری نیز کار کند .
این امر دو حالت می تواند داشته باشد :
- کار با اطلاعات در یک شیت مجزا .
- کار با اطلاعات در یک سند یا فایل اکسل مجزا
با هم به بررسی هر دوی این حالت ها می پردازیم .
فرض کنید اطلاعات جدول در مثال قبلی درون شیت مجزایی به نام “اطلاعات پرسنل ” قرار دارد
اکنون ما در شیتی به نام ” خلاصه سوابق ” می خواهیم به اطلاعات افراد دسترسی پیدا کنیم .
در این حالت تابع ما بصورت زیر تعریف می شود .
= VLOOKUP(A2; 'اطلاعات پرسنل! A2:D10';۴)
همانطور که در تابع فوق می توانید ببینید قبل از محدوده مورد جستجو نام شیت مورد نظر آورده شده است .
استفاده از وی لوکاپ در اکسل مجزا
برای اینکه شما بتوانید از تابع V Lookup به استخراج اطلاعات موجود در یک فایل اکسل دیگر بپردازید باید از فرمول عمومی زیر تبعیت نمایید .
نکته ای که در تصویر بالا باید به آن دقت نمود این است که حتما نام فایل مورد نظر باید در داخل یک [ ] قرار بگبرد .
برای اینکه بتوانید از این قابلیت استفاده کنید کافی است هر دو فایل اکسل خود را باز کنید .
اکنون برای آدرس دهی به راحتی می توانید به فایل و شیت مورد نظر خود اشاره نمایید .
بعنوان مثال فرض کنید که جدول بالا در فایل اکسل به نام “my excel” قرار دارد .
اکنون در فایل دوم می خواهیم با استفاده از تابع VLookup به جستجوی اطلاعات در فایل “my excel” بپردازیم .
برای این منظور می توانیم از تابع VLookup به شکل زیر استفاده کنیم .
تابع فوق وقتی که فایل اکسل مرجع ما بسته باشد نیز کار خواهد کرد .
تا به اینجا با هر سه آرگومان این تابع آشنا شدیم .
اکنون نوبت به بررسی آخرین آرگومان این تابع است .
ورودی آخر تابع ویلوکاپ چه کاربردی دارد ؟
همانگونه که گفتیم با استفاده از این آرگومان میزان دقت را برای این تابع مشخص می کنیم .
این پارامتر یکی از دو حالت زیر را می تواند بگیرد :
اگر این پارامتر را False در نظر بگیریم تابع فقط و فقط مقدار مورد نظر ما را برای ما برمی گرداند و نه هیچ چیز دیگر را. ناگفته نماند که اگر این پارامتر را تعیین نکنیم به حالت پیشفرض False در نظر گرفته می شود .
چند سوال پر تکرار در مورد تابع Vlookup در اکسل
در ادامه با هم به چند تا سوال پرکاربرد در مورد تابع ویلوکاپ در اکسل را بررسی می کنیم .
تابع وی لوکاپ چطوری بین ستون ها حرکت می کند ؟
حرکت تابع وی لوکاپ بصورت ستونی است . یعنی فقط ستون به ستون می تواند جستجو کند .
یک نکته مهم دیگر هم در خصوص حرکت تابع vlookup بین ستون ها وجود دارد و آن هم این است که این تابع فقط می تواند به سمت راست جابجا شود .
یعنی هر ستونی را که اولین آرگومان را در ان پیدا می کند را به عنوان ستون مبنا قرار داده و از همان ستون به تعداد ستونهایی که برایش مشخص کرده ایم به سمت راست جابجا می شود .
البته اگر صفحه شما راست چین باشد این تابع حرکتش به سمت چپ خواهد بود .
تابع ویلوکاپ در برابر حروف بزرگ و کوچک انگلیسی چطور عمل می کند ؟
این تابع نسبت به حروف بزرگ و کوچکی که در زبان انگلیسی از آنها استفاده می شود کاملا حساس است . یعنی بین حروف بزرگ و کوچک تفاوت قائل می شود .
تابع vlookup در اکسل چند تا داده را پیدا می کند ؟
یک نکته خیلی مهم در کار با تابع وی لوکاپ این است که اگر در یک محدوده چندین داده داشته باشید و شما با استفاده از تابع وی لوکاپ آن داده ها را جستجو کنید ، این تابع فقط و فقط اولین داده را برای شما بر می گرداند .
مثلا فرض کنید در یک جدول اسامی مربوط به 10 کتاب مختلف را داشته باشید . که نویسنده 4 تا از این کتابها یک نفر باشد .
حالا اگر با استفاده از تابع وی لوکاپ بخواهید اسم نویسنده را جستجو کرده و اطلاعات مربوط به کتابهای نویسنده را استخراج کنید تابع وی لوکاپ در حین جستجو اولین باری که با اسم نویسنده برخورد کند اطلاعات مربوط به همان دفعه اول را نمایش می دهد و دیگر به دنبال جستجو در سایر ردیف ها نمی رود .
این خاصیت تابع وی لوکاپ است که در بعضی از جاها واقعا کاربردی است اما در بعضی از جاها هم می تواند برای ما محدودیت ایجاد کند .
اما آیا می شود این محدودیت را برطرف کرد ؟
پاسخ این سوال مثبت است . ما در دوره مربوط به تکنیک های جستجوی حرفه ای در اکسل راجع به این قضیه آموزش مجزایی را تهیه کرده و در آنجا یاد می گیریم که چطور می شود این محدودیت را از این تابع رفع کرد .
آیا در تابع ویلوکاپ می شود از ویلد کاردها استفاده کرد ؟
پاسخ این سوال مثبت است و البته استفاده از این وایلد کاردها خیلی هم قدرت و مانور کار با تابع را بالا می برد . فقط باید بدانیم که کی و چطور می شود از آنها استفاده کرد .
اینکه چه موقع می شود از وایلد کاردها در تابع وی لوکاپ استفاده کرد باید گفت که هر موقع شما به دنبال یک عبارت خاص هستید اما همه کاراکترهای ان را به خاطر نمی آورید می توانید به جای کاراکترهای فراموش شده از وایلد کاردها استفاده کنید .
مثلا فرض کنید که دنبال یک کد ملی ده رقمی می گردید ولی فقط می دانید که کد ملی شما با سه رقم “079” شروع می شود .
حالا کافی است همین سه رقم را نوشته و در ادامه از وایلد کاردها استفاده کنید . دیگر بقیه کارها را به اکسل بسپارید .
در اینجا چند تا مثال از استفاده از آنها را بررسی می کنیم .
فرض کنید که به دنبال کد ملی می گردیم که با 079 شروع می شود .
=VLOOKUP("079???????", $A$3:$B$15, 3, FALSE)
اینجا ما دقیقا می دانیم که کد ملی ما برابر با 10 رقم است . حالا چون سه رقم اول آن را می دانیم همان ها را نوشته و برای مابقی ارقام از علامت سوال استفاده می کنیم .
دیدگاهتان را بنویسید